import pandas as pd
import time
import random
from create_spec import create_spec
from create import create
from store_image import store_image

excel_file_name = 'KTT-商品带图片导出.xlsx'

def row_to_json(row):
    good_ruler_2_value = row['商品规格2-值'].strip()
    good_ruler_2_value_list = good_ruler_2_value.splitlines()
    spec_map_for_create = {row['商品规格1-名称']:[row['商品规格1-值']],row['商品规格2-名称']:good_ruler_2_value_list}
    # print('spec_map_for_create', spec_map_for_create)
    create_spec_response = create_spec(spec_map_for_create)['result']
    # print('create_spec_response', create_spec_response)
    
    spec_id_spec_dto_map = {}
    for _, specs in create_spec_response['spec_map'].items():
        for spec in specs:
            spec_id_spec_dto_map[str(spec['spec_id'])] = {
                'spec_id': spec['spec_id'],
                'name': spec['name'],
                'parent_name': spec['parent_name']
            }
    # print('spec_id_spec_dto_map', spec_id_spec_dto_map)

    good_ruler_2_value_map = map(lambda x: {'name': x}, good_ruler_2_value_list)
    spec_map = [
        {
            'value': row['商品规格1-名称'],
            'list': [
                {
                    'name': row['商品规格1-值'],
                    'thumb_url': [],
                },
            ],
        },
        {
            'value': row['商品规格2-名称'],
            'list': list(good_ruler_2_value_map),
        },
    ]
    # print('spec_map', spec_map)
    
    sku_list = []
    spec_categories = list(create_spec_response['spec_map'].keys())
    if len(spec_categories) >= 2:
        first_category_specs = create_spec_response['spec_map'][spec_categories[0]]
        second_category_specs = create_spec_response['spec_map'][spec_categories[1]]

        # 生成组合
        for first_spec in first_category_specs:
            for second_spec in second_category_specs:
                sku_list.append({
                    'sku_material_id': None,
                    'thumb_url': row['商品规格1-图片'],
                    'price': int(row['团购价(元)'] * 100),
                    'quantity_type': 1,
                    'quantity': 10000000,
                    'remain_quantity': 10000000,
                    'reserve_quantity': 0,
                    'sold_quantity': 0,
                    'spec_id_list': [first_spec['spec_id'], second_spec['spec_id']],
                    'spec_list': [
                        {
                            'spec_id': first_spec['spec_id'],
                            'name': first_spec['name'],
                            'parent_name': first_spec['parent_name']
                        },
                        {
                            'spec_id': second_spec['spec_id'],
                            'name': second_spec['name'],
                            'parent_name': second_spec['parent_name']
                        }
                    ],
                    'cost_price': -1,
                    'total_quantity': 10000000,
                })
    else:
        raise ValueError("规格分类数量不足，无法进行组合。至少需要两个分类。")

    # print('sku_list', sku_list)

    return {
        "goods_name": row["商品名称 goods_name"],
        'goods_price': 0,
        "market_price": int(row["划线价(元) market_price"] * 100),
        'specification': '',
        'quantity_type': 1,
        'quantity': 70000000,
        'sold_quantity': 0,
        'remain_quantity': 70000000,
        'reserve_quantity': 0,
        'description': row["商品描述 description"],
        "category_id": row["商品分类 category_id"],
        'image_urls': [
            row["商品图片 image_url"]
        ],
        'labels': [],
        'sku_list': sku_list,
        'spec_map': spec_map,
        'spec_id_spec_dto_map': spec_id_spec_dto_map,
        'video_list': [],
        'goods_cover': row["商品图片 image_url"],
        'goods_material_type': 0,
        'goods_info_sync_status': 0,
        'has_quantity_sync_relation': False,
        'has_sold_quantity': False,
        'weight': 0,
        'sign': 'FEE79E11C4877EF7A9DB8C9C53FB1B3D',
        'signVersion': 'sv1',
        'timestamp': time.time() * 1000
    }

def sleep_random():
    time.sleep(random.uniform(4, 7))

# 读取 Excel 文件
# 读取第一个工作表
df = pd.read_excel(excel_file_name, sheet_name=0)

add_value = '已添加'
# 初始化计数器
added_count = 0
# 遍历每一行数据
for index, row in df.iterrows():
    print(row)
    # goods_name = row['商品名称 goods_name']
    # if pd.isnull(goods_name):
    #     print(f"商品名称为空 {index}: {goods_name}")
    #     continue

    # if row['是否添加'] == add_value:
    #     print(f"跳过商品{index}: {goods_name}")
    #     continue

    # print(f"创建商品{index+1}: {goods_name}")
    
    # json_data = row_to_json(row)
    # sleep_random()
    # create_resposne = create(json_data)
    # print(create_resposne)
    # if create_resposne['success'] & create_resposne['result']['success']:
    #     df.loc[index, '是否添加'] = add_value
    #     added_count += 1
    # else:
    #     print(f'创建失败{index}: {goods_name}')
    
    sleep_random()

# 使用 ExcelWriter 保存到原文件
if added_count > 0:
    try:
        df.to_excel(excel_file_name, index=False)
        print(f'保存 Excel 文件成功: {excel_file_name}')
    except PermissionError:
        print('保存 Excel 文件出错: 文件可能被其他程序占用，请关闭相关程序后再试。')
    except Exception as e:
        print(f'保存 Excel 文件出错: {e}')

input("按任意键退出...")